package com.chenyun.system.generator.config;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import com.chenyun.system.generator.model.GenTable;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * 存储静态的sql查询语句
 */
public class SqlTemplateConfig {
   public static ThreadLocal<String>  dataSourceLocal=new ThreadLocal<>();

    static String selectTableCountFiled="select count(*)  ";
    static String selectTabledataFiled="select table_name, table_comment, create_time, update_time   ";

    static String selectTableSql = "  from information_schema.tables\n" +
            "    where table_schema = (select database())\n" +
            "    AND table_name NOT LIKE 'qrtz_%'   AND table_name NOT LIKE 'gen_%'\n" ;

    static String selectTableSqlByNames = "select table_name, table_comment, create_time, update_time from information_schema.tables " +
            " where table_name NOT LIKE 'qrtz_%' " +
            " and table_name NOT LIKE 'gen_%' " +
            "  and table_schema = (select database()) " +
            "  and table_name in ( $in)";





    static String selectColumnSql="SELECT\n" +
            "  column_name,\n" +
            " ( CASE WHEN ( is_nullable = 'no' && column_key != 'PRI' ) THEN '1' ELSE NULL END ) AS is_required,\n" +
            " ( CASE WHEN column_key = 'PRI' THEN '1' ELSE '0' END ) AS is_pk,\n" +
            " ordinal_position AS sort,\n" +
            " column_comment,\n" +
            " ( CASE WHEN extra = 'auto_increment' THEN '1' ELSE '0' END ) AS is_increment,\n" +
            " column_type \n" +
            "FROM " +
            " information_schema.COLUMNS " +
            "WHERE\n" +
            " table_schema = ( SELECT DATABASE ( ) ) " +
            " AND table_name = ('$table')" +
            " ORDER BY ordinal_position";







    /**
     * 获取查询表 sql
     *
     * @param genTable
     * @return
     */
    public static String getSelectTableSql(GenTable genTable) {
        StringBuilder builder = new StringBuilder();
        builder.append(selectTabledataFiled+selectTableSql);
        builderSql(genTable,builder);
        return builder.toString();
    }

    /**
     * 获取查询表总条数 sql
     *
     * @param genTable
     * @return
     */
    public static String getSelectTableCountSql(GenTable genTable) {
        StringBuilder builder = new StringBuilder();
        builder.append(selectTableCountFiled+selectTableSql);
        final GenTable clone = ObjectUtil.clone(genTable);
        clone.getParams().remove("pageSize");
        builderSql(clone,builder);

        return builder.toString();
    }

    static  void  builderSql(GenTable genTable ,StringBuilder builder){
        if (StringUtils.isNotBlank(genTable.getTableName())) {
            builder.append("  AND lower(table_name) like lower(concat('%', '" + genTable.getTableName() + "', '%')) ");
        }
        if (StringUtils.isNotBlank(genTable.getTableComment())) {
            builder.append("  AND lower(table_comment) like lower(concat('%', '" + genTable.getTableComment() + "', '%'))");
        }
        if (Objects.nonNull(genTable.getParams().get("beginTime"))) {
            builder.append(" AND date_format(create_time,'%y%m%d') &gt;= date_format('" + genTable.getParams().get("beginTime") + "','%y%m%d')");
        }
        if (Objects.nonNull(genTable.getParams().get("endTime"))) {
            builder.append("  AND date_format(create_time,'%y%m%d') &lt;= date_format('" + genTable.getParams().get("endTime") + "','%y%m%d')");
        }
        // 组装查询
        if (CollectionUtil.isNotEmpty(genTable.getParams())&&Objects.nonNull(genTable.getParams().get("tables"))){
            builder.append(" and  table_name not in ("+genTable.getParams().get("tables").toString()+")");
        }
        builder.append("   order by create_time desc");
        if (CollectionUtil.isNotEmpty(genTable.getParams())&&Objects.nonNull(genTable.getParams().get("pageSize"))){
            //计算跳过数
            final int pageSize = Integer.parseInt(genTable.getParams().get("pageSize").toString());
            final int pageNum = Integer.parseInt(genTable.getParams().get("pageNum").toString());

            int start=(pageNum-1)*pageSize;
            int end =pageSize*pageNum;
            builder.append(" limit "+start+","+end+"");
        }

    }



    /**
     * 查询表sql
     * @param tableNames
     * @return
     */
    public static String getSelectTableSqlByNames(String[] tableNames) {
        List<String> list = new ArrayList<>();
        for (int i = 0; i < tableNames.length; i++) {
            list.add(" '" + tableNames[i] + "' ");
        }
        String join = String.join(",", list);
        String sql = selectTableSqlByNames.replace("$in", join);
        return sql;


    }

    public static String getSelectColumnSqlByName(String tableName){
        return  selectColumnSql.replace("$table",tableName);
    }


}
